package cn.com.zp.util.excel;

import java.lang.reflect.Method;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRichTextString;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import cn.com.zp.util.base.StringUtils;

/**
 * 生成Excel帮助工具类
 */
public class ExcelUtils {

	/**
	 * 生成excel工作簿
	 * 
	 * @param excelName
	 *            生成后的页卡名
	 * @param titles
	 *            格式为{标题1={属性1.属性1},标题2={属性2}} 与EL表达式相似
	 * @param list
	 *            数据
	 * @return
	 */
	@SuppressWarnings({ "deprecation", "rawtypes" })
	public static HSSFWorkbook instanceExcel(String excelName, String[] titles,
			List list) {
		HSSFWorkbook workbook = new HSSFWorkbook(); // 产生工作簿对象
		HSSFSheet sheet = workbook.createSheet(); // 产生工作表对象
		HSSFCellStyle style = workbook.createCellStyle();
		style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
		// 为了工作表能支持中文，设置字符编码为UTF_16
		// workbook.setSheetName(0, excelName, HSSFCell.ENCODING_UTF_16);
		workbook.setSheetName(0, excelName);//
		// 产生第一行字段头
		HSSFRow row = sheet.createRow((short) 0);
		// 表达式
		String[] el = separateTitleAndEl(titles);
		// 设置长度
		for (int i = 0; i < titles.length; i++)
			sheet.setColumnWidth((short) i, (short) 5000);
		addCell(row, titles);

		String[] columnText = null;
		if (list != null) {
			for (int i = 0; i < list.size(); i++) {
				// 每条数据产生新的一行
				row = sheet.createRow((short) i + 1);
				columnText = new String[el.length];
				// 逐一取得表达对应值
				for (int j = 0; j < el.length; j++) {
					// 表达式对应结果
					String value = analyzeExpression(el[j], list.get(i));
					columnText[j] = value;
				}

				addCell(row, columnText);
			}
		}
		return workbook;
	}

	/**
	 * 添加一行单元格
	 * 
	 * @param row
	 * @param columnNames
	 *            一行中的各列值
	 */
	@SuppressWarnings("deprecation")
	public static void addCell(HSSFRow row, String[] columnNames) {
		for (int i = 0; i < columnNames.length; i++) {
			HSSFCell cell = row.createCell((short) i);
			HSSFRichTextString column = new HSSFRichTextString(
					StringUtils.trimToBlank(columnNames[i]));
			cell.setCellValue(column);
			cell.setCellType(HSSFCell.CELL_TYPE_STRING);
			// cell.setEncoding(HSSFCell.ENCODING_UTF_16);
		}
	}

	/**
	 * 通过反射调用对象方法
	 * 
	 * @param methodName
	 * @param o
	 *            对象/或类对象
	 * @param clazz
	 *            方法参数类型数组
	 * @param parameter
	 *            方法输入参数数组
	 * @return
	 */
	@SuppressWarnings("rawtypes")
	public static Object reflectInvoke(String methodName, Object o,
			Class[] clazz, Object[] parameter) {
		// String .
		if (o == null)
			return null;
		try {
			if (o instanceof Class) {
				o = o.getClass().newInstance();
			}
			Method method = o.getClass().getMethod(methodName, clazz);
			// 返回结果
			return method.invoke(o, parameter);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println("---" + ExcelUtils.class.getName()
					+ "--reflectInvoke----反射调用方法{" + methodName
					+ "}出错...------");
		}
		return null;
	}

	/**
	 * 解析表达式
	 * 
	 * @param expression
	 * @param object
	 * @return
	 */
	public static String analyzeExpression(String expression, Object object) {
		int startIndex = expression.indexOf('{');
		int endIndex = expression.indexOf('}');
		String el = expression.substring(startIndex + 1, endIndex);
		if (el.trim().length() == 0) {
			System.out.println("---method:analyzeExpression---Error----");
			return "";
		}
		String[] methodNames = el.trim().split("\\.");
		for (int i = 0; i < methodNames.length; i++) {
			// 调用对象get方法
			String methodName = "get"
					+ methodNames[i].trim().substring(0, 1).toUpperCase();
			if (methodNames[i].trim().length() > 1)
				methodName += methodNames[i].trim().substring(1,
						methodNames[i].trim().length());
			object = reflectInvoke(methodName, object, null, null);
		}
		if (object == null)
			return "";
		return object.toString();
	}

	/**
	 * 分离标题中的表达式
	 * 
	 * @return
	 */
	public static String[] separateTitleAndEl(String[] columnTitles) {
		String[] el = new String[columnTitles.length];
		for (int i = 0; i < columnTitles.length; i++) {
			String[] keyValue = columnTitles[i].trim().split("=");
			columnTitles[i] = keyValue[0];
			el[i] = keyValue[1];
		}
		return el;
	}

	// 以下是测试代码
	/*
	 * response.setHeader("Content-Disposition",
	 * "attachment;filename=\"problem.xls\""); String[] columnNames = {
	 * "设备分类={deviceInfo.clientType}","设备型号={deviceInfo.deviceModel}",
	 * "操作系统版本={deviceInfo.deviceSystem}","设备分辨率={deviceInfo.deviceResolution}",
	 * "用户账号={userAccount}","所属二级机构={orgName}","反馈主题={problemTheme}",
	 * "反馈内容={problemContext}","问题类型={problemTypeStr}","反馈时间={createTimeStr}",
	 * "当前状态={problemStatusStr}","回复时间={replyTime}"}; //调用工具类生成excel表格
	 * HSSFWorkbook book = ExcelUtil.instanceExcel("问题反馈统计", columnNames,
	 * problemInfos); book.write(response.getOutputStream());
	 * response.getOutputStream().flush();
	 */
}
